﻿using System;
using CardLogistics.Web.Components;
using System.Collections;
using System.Text;
using CardLogistics.DAL.CardRecovery;
using System.Data;
using System.Web;
using System.IO;
using System.Collections.Generic;
using System.Net;
using System.Text.RegularExpressions;


namespace CardLogistics.Web.MainPage.CardRecovery.Scrap
{
    public partial class AddEdit : BasePage
    {
        public string TitleName = "";
        private static readonly CARDRECOVERY_SCRAPData DAL = new CARDRECOVERY_SCRAPData();
        private static readonly CARDRECOVERY_SCRAP_DETAILData DDAL = new CARDRECOVERY_SCRAP_DETAILData();
        /// <summary>
        /// 增加的行
        /// </summary>
        protected StringBuilder AddTr = new StringBuilder();

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                SCRAPDATA.Value = DateTime.Now.ToString("yyyy-MM-dd");//报废日期
                InitControl();
            }
        }

        #region 保存/提交
        protected void btn_Submit_Click(object sender, EventArgs e)
        {
            try
            {
                GetListInfo(1);
            }
            catch (Exception ex)
            {
                ShowMessageBox("系统异常:" + ex.ToString());
            }
        }

        protected void btn_Add_Click(object sender, EventArgs e)
        {
            try
            {
                GetListInfo(0);
            }
            catch (Exception ex)
            {
                ShowMessageBox("系统异常:" + ex.ToString());
            }
        }
        /// <summary>
        /// 获取执行的SQL语句集合
        /// </summary>
        /// <param name="isSubmit">0 未提交 1 提交</param>
        private void GetListInfo(int isSubmit)
        {   
            string message = "";//提示信息
            string err = ""; //提示信息
            int startCardNO = 0; //开始卡号
            int endCardNO = 0; //结束卡号
            string gid = "";//主键ID
            ArrayList mylist = new ArrayList();
            if (!IsEdit)//新增
            {
                gid = System.Guid.NewGuid().ToString();
                mylist.Add(" INSERT INTO CARDRECOVERY_SCRAP(ID,SCRAPNO,SCRAPDATA,REMARKS,LASTUPDATEUSER,ISSUBMIT) values('" + gid + "','" + SCRAPNO.Value.Trim() + "',to_date('" + SCRAPDATA.Value.Trim() + "','yyyy-mm-dd hh24:mi:ss'),'" + REMARKS.Value.Trim() + "','" + LoggedUser.ID + "'," + isSubmit + ") ");
            }
            else//编辑操作
            {
                gid = Request.QueryString["id"];
                mylist.Add(" UPDATE CARDRECOVERY_SCRAP SET SCRAPNO='" + SCRAPNO.Value.Trim() + "',SCRAPDATA=to_date('" + SCRAPDATA.Value.Trim() + "','yyyy-mm-dd hh24:mi:ss'),REMARKS='" + REMARKS.Value.Trim() + "',LASTUPDATEUSER='" + LoggedUser.ID + "',LASTUPDATEDATE=sysdate,ISSUBMIT=" + isSubmit + " WHERE ID='" + gid + "' ");
                mylist.Add(" delete from CARDRECOVERY_SCRAP_DETAIL where SCRAPID ='" + gid + "'");
            }
            string FileValue = hf_detailId.Value; //报废入库明细字段
            string[] split = FileValue.Split(';');//以分号分隔

            DataTable dtCard=new DataTable();
            dtCard.Columns.Add("startCardNO");
            dtCard.Columns.Add("endCardNO");

            for (int i = 0; i < split.Length; i++)
            {
                StringBuilder strSql = new StringBuilder();//初始化
                strSql.Append("insert into CARDRECOVERY_SCRAP_DETAIL");
                strSql.Append("(SCRAPID,StartCardNo,EndCardNo,CardAmount)");
                strSql.Append(" values(");
                strSql.Append("'" + gid + "',");

                 startCardNO = int.Parse(split[i].Split('|')[0]);
                 endCardNO=  int.Parse(split[i].Split('|')[1]);

                 StringBuilder strSqlY = new StringBuilder();
                 strSqlY.Append("select * from cardrecovery_scrap_detail");
                 strSqlY.Append(" where ((to_number('" + startCardNO + "') between to_number(StartCardNo) and to_number(EndCardNo))");
                 strSqlY.Append(" or (to_number('" + endCardNO + "') between to_number(StartCardNo) and to_number(EndCardNo))");
                 strSqlY.Append(" or (to_number(StartCardNo) between to_number('" + startCardNO + "') and to_number('" + endCardNO + "')))");
                 strSqlY.Append(" and scrapid not in (select ID from cardrecovery_scrap where Issubmit=4)");//过滤审批未通过的明细卡号
                 if (IsEdit)
                 {
                     //修改时，则过滤本条数据明细记录
                     strSqlY.Append(" and scrapid!='" + Request.QueryString["Id"] + "'");
                 }
                 DataTable res = CardLogistics.DAL.DbHelperSQL.GetQueryResult(strSqlY.ToString());
                 if (res != null && res.Rows.Count > 0)
                 {
                     message += "第" + (i + 1) + "行 ";
                     goto k;
                 }
                strSql.Append("'" + split[i].Split('|')[0] + "','" + split[i].Split('|')[1] + "'," + split[i].Split('|')[2] + "");
                strSql.Append(")");
                #region 插入卡明细
                string values = startCardNO + "," + endCardNO + ",'0',0" + ",'" + gid + "'," + "to_date('"+Convert.ToDateTime(SCRAPDATA.Value.Trim()).ToString("yyyy-MM-dd") +"','yyyy-MM-dd')," + "8," + i+",0";
                string strP = "begin p_cardrecord_add(" + values + "); end;";
                #endregion
                mylist.Add(strSql.ToString());
                mylist.Add(strP);
            }

            k://定义goto
            if (message != "")
            {
                ShowMessageBox(message + "卡号重复！");
            }
            else if (err != "")
            { 
                 ShowMessageBox("开始卡号[" + startCardNO + "]至结束卡号[" + endCardNO + "]在库中不存在!");
            }
            else 
            {
                int result = DAL.AddOrEdit(mylist);
                if (result > 0)
                {
                    if (!IsEdit)
                    {
                        ChgBillNumber("报废入库");
                    }
                    if (isSubmit == 1)
                    {
                        InsertAppInfo(13, gid); // 13报废入库
                        InsertLog("保存并提交报废入库,单号:" + SCRAPNO.Value.Trim());
                    }
                    else
                    {
                        InsertLog("保存报废入库,单号:" + SCRAPNO.Value.Trim());
                    }
                     ReturnListInfo();
                }
                else
                {
                    ShowMessageBox("操作失败请重新操作！");
                }
            }
        }
        #endregion

        #region 编辑时绑定值
        /// <summary>
        /// 编辑时绑定值
        /// </summary>
        public void InitControl()
        {
            TitleName = GetStatusInfo(0, "报废入库");
        
            if (IsEdit)
            {
                string _key = Request.QueryString["id"];
                if (!string.IsNullOrEmpty(_key))
                {
                    CardLogistics.Model.CardRecovery.CARDRECOVERY_SCRAP pData = DAL.GetModel(_key);
                    SetWebControls(this.Page, pData);
                    DataTable dt = DDAL.GetInfoByScrapId(pData.ID);
                    string detId = "";
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        foreach (DataRow dr in dt.Rows)
                        {
                            detId += dr["StartCardNo"].ToString() + "|" + dr["EndCardNo"].ToString() + "|" + dr["CardAmount"].ToString() + ";";
                        }
                        AddRows(dt);//动态添加行
                        hf_detailId.Value = detId.TrimEnd(';');
                    }
                    btn_Add.Visible = btn_Submit.Visible = false;
                    if (pData.ISSUBMIT == 0 || pData.ISSUBMIT == 3)//状态为 0(未提交) 或 3(驳回)时才可以进行编辑操作
                    {
                        btn_Add.Visible = btn_Submit.Visible = true;
                    }
                    TitleName = GetStatusInfo(int.Parse(pData.ISSUBMIT.ToString()), "报废入库");
                }
            }
            else
            {
                SCRAPNO.Value = GetBillNumber("报废入库");
            }

        }
        #endregion

        #region 报废入库导入
        /// <summary>
        /// 报废入库导入
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        //static object basePath = "~/MainPage/File/ScrapUpFile/"; //读取Excel的路径
        protected void btnImport_Click(object sender, EventArgs e)
        {
            string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();//获得文件的扩展名
            if (IsXls != ".xls" && IsXls != ".xlsx")
            {
                ShowMessageBox("只可以选择Excel文件");
                return;
            }
            HttpPostedFile file = this.FileUpload1.PostedFile;

            string filename = FileUpload1.FileName;//获取Execle文件名
            string filePath = Server.MapPath("~/MainPage/File/ScrapUpFile/" + FileUpload1.FileName);
            file.SaveAs(filePath);
            FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
            StreamReader sr = new StreamReader(fs, Encoding.GetEncoding("utf-8"));
            try
            {
                //第一种方法：传统方法，采用OleDB读取EXCEL文件 
                //优点：写法简单，缺点：服务器必须安有此组件才能用，不推荐使用

                //DataSet ds = GetDataFromExcel(filePath, filename);  //调用自定义方法
                //DataTable dt = ds.Tables[0];

                //第二种方法：用第三方组件：NPOI组件,推荐使用此方法
                //DataTable dt = ReadExcel(basePath, filename);
                DataTable dt = ExcelHelper.GetDataTable(filePath);

                //循环移除DataTable中的空行
                List<DataRow> removelist = new List<DataRow>();
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    bool rowIsNull = true; //标记是否有空行
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        if (!string.IsNullOrEmpty(dt.Rows[i][j].ToString().Trim()))
                        {
                            rowIsNull = false;
                        }
                    }
                    if (rowIsNull)
                    {
                        removelist.Add(dt.Rows[i]);
                    }
                }
                for (int i = 0; i < removelist.Count; i++)
                {
                    dt.Rows.Remove(removelist[i]);
                }
                string detId = "";
                if (dt != null && dt.Rows.Count > 0)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        detId += dr["开始卡号"].ToString() + "|" + dr["结束卡号"].ToString() + "|" + dr["数量"].ToString() + ";";
                    }
                    AddRows(dt);//动态添加行
                    hf_detailId.Value = detId.TrimEnd(';');
                }
            }
            catch
            { }
            finally
            {
                fs.Flush(); //释放流
                fs.Close();//关闭流
            }
        }
        #endregion

        #region 查询报废入库数据
        /// <summary>
        /// 查询报废入库数据
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnSel_Click(object sender, EventArgs e) 
        {
            try
            {
                string startDate = txt_StartDate.Text.Replace("-", ""); //开始日期
                string endDate = txt_EndDate.Text.Replace("-", "");//结束日期

                string strUrl = HttpGet("http://192.168.0.173:8087/api/logistics/GetScrapCard?bgTime=" + startDate + "&endTime=" + endDate + "");
                DataTable dt = JsonToDataTable(strUrl.Replace(@"\", ""));
                string detId = "";
                if (dt != null && dt.Rows.Count > 0)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        detId += dr["cardno"].ToString() + "|" + dr["cardno"].ToString() + "|" + Convert.ToInt32(Convert.ToDouble(dr["cdnum"].ToString())) + ";";
                    }
                    AddRows(dt);//动态添加行
                    hf_detailId.Value = detId.TrimEnd(';');//给隐藏文本赋值
                    lb_Info.Text = "";//清空提示信息
                }
                else
                {
                    lb_Info.Text = "未查到报废数据！";//清空提示信息
                    hf_detailId.Value = "";//清空隐藏文本值
                }
            }
            catch (Exception ex)
            {
                ShowMessageBox("查询异常:" + ex.ToString());
            }
        }

        /// <summary>
        /// 转换网址
        /// </summary>
        /// <param name="url"></param>
        /// <returns></returns>
        public static string HttpGet(string url)
        {
            Encoding encoding = Encoding.UTF8;
            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
            request.Method = "GET";
            request.Accept = "textml, application/xhtml+xml, */*";
            request.ContentType = "application/json";
            HttpWebResponse response = (HttpWebResponse)request.GetResponse();
            using (StreamReader reader = new StreamReader(response.GetResponseStream(), Encoding.UTF8))
            {
                return reader.ReadToEnd();
            }
        }

        /// <summary>
        /// 将json转换为DataTable
        /// </summary>
        /// <param name="strJson">得到的json</param>
        /// <returns></returns>
        private DataTable JsonToDataTable(string strJson)
        {
            //转换json格式
            strJson = strJson.Replace(",\"", "*\"").Replace("\":", "\"#").ToString();
            //取出表名   
            var rg = new Regex(@"(?<={)[^:]+(?=:\[)", RegexOptions.IgnoreCase);
            string strName = rg.Match(strJson).Value;
            DataTable tb = null;
            //去除表名   
            strJson = strJson.Substring(strJson.IndexOf("[") + 1);
            strJson = strJson.Substring(0, strJson.IndexOf("]"));
            //获取数据   
            rg = new Regex(@"(?<={)[^}]+(?=})");
            MatchCollection mc = rg.Matches(strJson);
            for (int i = 0; i < mc.Count; i++)
            {
                string strRow = mc[i].Value;
                string[] strRows = strRow.Split('*');
                //创建表   
                if (tb == null)
                {
                    tb = new DataTable();
                    tb.TableName = strName;
                    foreach (string str in strRows)
                    {
                        var dc = new DataColumn();
                        string[] strCell = str.Split('#');
                        if (strCell[0].Substring(0, 1) == "\"")
                        {
                            int a = strCell[0].Length;
                            dc.ColumnName = strCell[0].Substring(1, a - 2);
                        }
                        else
                        {
                            dc.ColumnName = strCell[0];
                        }
                        tb.Columns.Add(dc);
                    }
                    tb.AcceptChanges();
                }
                //增加内容   
                DataRow dr = tb.NewRow();
                for (int r = 0; r < strRows.Length; r++)
                {
                    dr[r] = strRows[r].Split('#')[1].Trim().Replace("，", ",").Replace("：", ":").Replace("\"", "");
                }
                tb.Rows.Add(dr);
                tb.AcceptChanges();
            }
            return tb;
        }
        #endregion

        #region 模板下载
        /// <summary>
        /// 模板下载
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnDownLoad_Click(object sender, EventArgs e)
        {
            string excelFile = Server.MapPath("~/MainPage/File/DownLoad/报废入库导入模板.xls");
            DownLoad(excelFile, "报废入库导入模板.xls");
        }
        #endregion

        #region 动态添加行
        /// <summary>
        /// 动态添加行
        /// </summary>
        /// <param name="dt"></param>
        private void AddRows(DataTable dt)
        {
            //动态创建行
            for (int i = 11; i <= dt.Rows.Count; i++)
            {
                AddTr.Append("<tr><td align='center'>" + i + "</td>");
                AddTr.Append("<td><input name='StartCardNo' type='text' class='dfinput' onkeydown='onlyNum();' maxlength='8'/> </td>");
                AddTr.Append("<td><input name='EndCardNo' type='text' class='dfinput' onkeydown='onlyNum();' maxlength='8'/></td>");
                AddTr.Append("<td><input name='CardAmount' type='text' class='dfinput' /></td>");
                AddTr.Append("<td>&nbsp;</td></tr>");
            }
        }
        #endregion
    }
}